<?php

class DataTables
{
    /**
     * @var string 表名
     */
    public $table;

    /**
     * @var array 显示列
     */
    public $columns;

    /**
     * @var string 查询的字段，默认*
     */
    public $fields = '*';

    /**
     * @var array 请求数据，默认$_POST
     */
    public $request;

    /**
     * @var string 查询条件
     */
    public $where = '';

    /**
     * @var string 筛选条件
     */
    public $filter = '';

    /**
     * @var string 默认排序
     */
    public $order = '';

    /**
     * @var string 分组
     */
    public $group = '';

    /**
     * @var string 组合后的查询条件
     */
    public $query = '';

    public function __construct(&$params = array())
    {
        isset($params['table']) && $this->table = $params['table'];
        isset($params['columns']) && $this->columns = $params['columns'];
        isset($params['fields']) && $this->fields = $params['fields'];
        isset($params['where']) && $this->where = $params['where'];
        isset($params['filter']) && $this->filter = $params['filter'];
        isset($params['order']) && $this->order = $params['order'];
        isset($params['group']) && $this->group = $params['group'];
        $this->request = &$_POST;
    }

    /**
     * Create the data output array for the DataTables rows
     *
     * @param  array $result Data from the SQL get
     * @return array          Formatted data in a row based format
     */
    protected function data_output($result)
    {
        $out = array();

        foreach ($result as $data)
        {
            $row = array();
            foreach ($this->columns as $column)
            {
                $field = $column['db'];
                if (isset($column['formatter']))
                {
                    $row[$field] = $column['formatter']($data[$field], $data);
                }
                else
                {
                    $row[$field] = $data[$field];
                }
            }
            $out[] = $row;
        }

        return $out;
    }

    /**
     * Paging
     *
     * Construct the LIMIT clause for server-side processing SQL query
     *
     * @return string SQL limit clause
     */
    protected function limit()
    {
        $limit = '';

        $start = isset($this->request['start']) ? intval($this->request['start']) : 0;
        $length = isset($this->request['length']) ? intval($this->request['length']) : 0;
        if ($start >= 0 && $length > 0)
        {
            $limit = "limit $start,$length";
        }

        return $limit;
    }


    /**
     * Ordering
     *
     * Construct the ORDER BY clause for server-side processing SQL query
     *
     * @return string SQL order by clause
     */
    protected function order()
    {
        $order = $this->order;

        if (isset($this->request['order']) && !empty($this->request['order']))
        {
            $orderBy = array();
            $dtColumns = $this->pluck($this->columns, 'dt', 'db');

            foreach ($this->request['order'] as $i => $order)
            {
                $columnIdx = intval($order['column']);
                $requestColumn = $this->request['columns'][$columnIdx];
                if (isset($dtColumns[$columnIdx]) && $requestColumn['orderable'] == 'true')
                {
                    $dir = $order['dir'] == 'asc' ? 'asc' : 'desc';
                    $orderBy[] = "{$dtColumns[$columnIdx]} $dir";
                }
            }

            if ($orderBy)
            {
                $order = implode(', ', $orderBy);
            }
        }

        return empty($order) ? '' : 'order by ' . $order;;
    }


    /**
     * Searching / Filtering
     *
     * Construct the WHERE clause for server-side processing SQL query.
     *
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here performance on large
     * databases would be very poor
     *
     * @return string SQL where clause
     */
    protected function filter()
    {
        $globalSearch = array();
        $columnSearch = array();
        $dtColumns = $this->pluck($this->columns, 'db');

        if (isset($this->request['search']) && !empty($this->request['search']['value']))
        {
            $str = trim($this->request['search']['value']);

            foreach ($this->request['columns'] as $column)
            {
                if (isset($dtColumns[$column['data']]) && $column['searchable'] == 'true')
                {
                    $index = $dtColumns[$column['data']];

                    $binary = isset($this->columns[$index]['bin']) && $this->columns[$index]['bin'] ? 'binary' : '';
                    $globalSearch[] = "{$this->_get_field($index)} like $binary '%$str%'";

                }
            }
        }

        // Individual column filtering
        if (isset($request['columns']))
        {
            foreach ($this->request['columns'] as $column)
            {
                $str = trim($column['search']['value']);
                if (isset($dtColumns[$column['data']]) && $column['searchable'] == 'true' && !empty($str))
                {
                    $index = $dtColumns[$column['data']];
                    $binary = isset($this->columns[$index]['bin']) && $this->columns[$index]['bin'] ? 'binary' : '';
                    $columnSearch[] = "{$this->_get_field($index)} like $binary '%$str%'";

                }
            }
        }

        // Combine the filters into a single string
        $where = '';

        if ($globalSearch)
        {
            $where = '(' . implode(' or ', $globalSearch) . ')';
      
        }

        if ($columnSearch)
        {
            $where = ($where === '' ? '' : ' and ') . implode(' and ', $columnSearch);
        
        }

        if ($where)
        {
            $where = 'where ' . $where;
        
        }

        return $where;
    }

    /**
     * The difference between this method and the `simple` one, is that you can
     * apply additional `where` conditions to the SQL queries. These can be in
     * one of two forms:
     *
     * * 'Result condition' - This is applied to the result set, but not the
     *   overall paging information query - i.e. it will not effect the number
     *   of records that a user sees they can have access to. This should be
     *   used when you want apply a filtering condition that the user has sent.
     * * 'All condition' - This is applied to all queries that are made and
     *   reduces the number of records that the user can access. This should be
     *   used in conditions where you don't want the user to ever have access to
     *   particular records (for example, restricting by a login id).
     *
     * @param  boolean $is_get_all whether to get all data
     * @return array          Server-side processing response array
     */
    public function search($is_get_all = false)
    {
        $whereAllSql = '';

        // Build the SQL query string from the request
        $limit = $this->limit();
        $order = $this->order();
        $where = $this->filter();
        $group = $this->group ? "group by $this->group" : '';

        if ($this->where)
        {
            $where .= ($where ? ' and ' : ' where ') . $this->where;
        }

        if ($this->filter)
        {
            $where .= ($where ? ' and ' : ' where ') . $this->filter;
            $whereAllSql = 'where ' . $this->where;
        }

        $this->query = $where;


        if ($is_get_all)
        {
            $sql = "select $this->fields from $this->table $where $group $order";
            $data = DB::select_db(1, $sql, 1);
            return $this->data_output($data);
        }

        // Main query to actually get the data
        $sql = "select $this->fields from $this->table $where $group $order $limit";
        $data = DB::select_db(1, $sql, 1);
        // Data set length after filtering
        $sql = "select count(*) as t from $this->table $where $group";
        $res = DB::select_db(1, $sql, 0);
        $recordsFiltered = isset($res['t']) ? intval($res['t']) : 0;


        if ($whereAllSql)
        {
            // Total data set length
            $sql = "select count(*) as t from $this->table $whereAllSql $group";
            $res = DB::select_db(1, $sql, 0);
            $recordsTotal = isset($res['t']) ? intval($res['t']) : 0;
        }
        else
        {
            $recordsTotal = $recordsFiltered;
        }

        /*
         * Output
         */
        return array(
            "draw" => isset ($this->request['draw']) ? intval($this->request['draw']) : 0,
            "recordsTotal" => intval($recordsTotal),
            "recordsFiltered" => intval($recordsFiltered),
            "data" => $this->data_output($data)
        );
    }

    /**
     * Pull a particular property from each assoc. array in a numeric array,
     * returning and array of the property values from each item.
     *
     * @param  array $array Array to get data from
     * @param  string $prop Property to read
     * @return array        Array of property values
     */
    protected function pluck($array, $prop, $key = '')
    {
        $out = array();

        $has_key = !empty($key);
        foreach ($array as $i => $arr)
        {
            $out[isset($arr[$prop]) ? $arr[$prop] : $i] = $has_key && isset($arr[$key]) ? $arr[$key] : $i;
        }

        return $out;
    }

    private function _get_field($index)
    {
        if (isset($this->columns[$index]))
        {
            $array = $this->columns[$index];
            $tb = isset($array['tb']) ? trim($array['tb'], '.') : '';
            return strpos($tb, '.') ? $tb : ($tb ? $tb . '.' : '') . $array['db'];
        }
        return 'ID';
    }


    /**
     * Return a string from an array or a string
     *
     * @param  array|string $array Array to join
     * @param  string $join Glue for the concatenation
     * @return string Joined string
     */
    protected function _flatten($array, $join = ' and ')
    {
        if (!$array)
        {
            return '';
        }
        else if ($array && is_array($array))
        {
            return implode($join, $array);
        }
        return $array;
    }
}
